select
	business_disp_cate2
	,business_line
	,new_disp_cate2
	,new_info_cate2
	,sloc1
	,slot
	,delivery_cnt as detailaction_delivery_cnt
	,delivery_uv as detailaction_delivery_uv
	,round(if(delivery_cnt/click_cnt is null,0,delivery_cnt/click_cnt),5) as detailaction_cvr_0
from (
	select
		business_disp_cate2
		,business_line
		,new_disp_cate2
		,new_info_cate2
		,sloc1
		,slot
		,APPROX_COUNT_DISTINCT(if(action_type = 'real_exposure', if(ad_id != '-' and ad_id != '-999999' and length(ad_id) > 1,recsid,null), null)  ) as exp_epv
		,APPROX_COUNT_DISTINCT(if(action_type = 'real_exposure', CONCAT_WS(',',recsid,slot,ad_id), null)) as exp_as
		,APPROX_COUNT_DISTINCT( if(action_type = 'real_exposure', case when platform=3 and imei<>'-' then imei when platform in(1,2) and length(cookieid) >5 then cookieid else NULL END,null)) as exp_uv
		,sum(if(action_type = 'info_detail', 1, 0)) as click_cnt -- 点击量
		,APPROX_COUNT_DISTINCT( if(action_type = 'info_detail', case when platform=3 and imei<>'-' then imei when platform in(1,2) and length(cookieid) >5 then cookieid else NULL END,null)) as click_uv
		,sum(if(action_type = 'resumedelivery', 1, 0)) as delivery_cnt -- 投递量
		,APPROX_COUNT_DISTINCT( if(action_type = 'resumedelivery', case when platform=3 and imei<>'-' then imei when platform in(1,2) and length(cookieid) >5 then cookieid else NULL END,null)) as delivery_uv
	from (
		SELECT
			coalesce(ii.action_type, 'search') as action_type
			,oo.ad_disptype
			,oo.business_line
			,oo.platform
			,oo.scate2
			,oo.sloc1
			,oo.slot
			,oo.recsid
			,oo.parthasinfo
			,oo.imei
			,oo.cookieid
			,oo.ad_id
			,business_disp_cate2
			,new_disp_cate2
			,new_info_cate2
		from (
			SELECT
				case when business_disp_cate2 is NULL or business_disp_cate2 = '' then '-' else business_disp_cate2 end as business_disp_cate2
				,case when new_disp_cate2 is NULL or new_disp_cate2 = ''  then '-' when business_disp_cate4 is null or business_disp_cate4 in ('-', '-999999', '') then '-' else new_disp_cate2 end as new_disp_cate2
				,case when new_info_cate2 is NULL or new_info_cate2 = '' then '-' else new_info_cate2 end as new_info_cate2
				,case WHEN ad_disptype is NULL or ad_disptype = '' then '-' else ad_disptype END as ad_disptype
				,case WHEN  business_line is NULL  then '-' else business_line end as business_line
				,case WHEN  platform is NULL or platform = '' then '-' else platform end as platform
				,case WHEN  scate2 is NULL or  scate2 = '' then '-' else scate2 end as scate2
				,case WHEN  sloc1 is NULL or sloc1 = '' then '-' else sloc1 end as sloc1
				,case WHEN  slot is NULL or slot = '' then '-' else slot end as slot
				,recsid
				,parthasinfo
				,imei_md5 as imei
				,cookieid
				,ad_id
				,'search' as action_type
			FROM (
			select
			 distinct business_disp_cate2
			,new_disp_cate2
			,new_info_cate2
            ,business_disp_cate4
			,stime
		    ,business_line
			,platform
			,disp_cate2 as scate2
			,disp_local1 as sloc1
			,slot
			,sid as recsid
			,if(cast(info_id as int) > 0, 1, 0 )  as parthasinfo
			,imei_md5
			,cookieid
			,info_type as ad_disptype
			,info_id as ad_id
			from hdp_lbg_supin_dwd_zp_ganji_zzlist hlsdzgz2
                        where stime  >= timestamp('<zx-start-time>') and stime  <= timestamp('<zx-end-time>') and sid != '-' and info_id != '-' and slot != '-' and info_type != '-'  AND trim(slot) != ''
			) as mm
		) as oo
		 join (

			select
				distinct recsid,
				ad_id,
				slot,
				ad_disptype,
				'resumedelivery' as action_type
			from hdp_lbg_supin_dwd_zp_resumedelivery
			where e_time >= timestamp('<action-start-time>') and e_time <= timestamp('<action-end-time>') and recsid != '-' and ad_id != '-' and slot != '-' and ad_disptype != '-'
		) as ii
		on oo.slot = ii.slot and oo.recsid = ii.recsid and oo.ad_id = ii.ad_id) as tmp
	GROUP by
			GROUPING SETS (
				(business_disp_cate2),
				(business_disp_cate2,business_line),
				(business_disp_cate2,new_disp_cate2),
				(business_disp_cate2,new_info_cate2),
				(business_disp_cate2,sloc1),
				(business_disp_cate2,slot),
				(business_disp_cate2,business_line,new_disp_cate2),
				(business_disp_cate2,business_line,new_info_cate2),
				(business_disp_cate2,business_line,sloc1),
				(business_disp_cate2,business_line,slot),
				(business_disp_cate2,new_disp_cate2,new_info_cate2),
				(business_disp_cate2,new_disp_cate2,sloc1),
				(business_disp_cate2,new_disp_cate2,slot),
				(business_disp_cate2,new_info_cate2,sloc1),
				(business_disp_cate2,new_info_cate2,slot),
				(business_disp_cate2,sloc1,slot),
				(business_disp_cate2,business_line,new_disp_cate2,new_info_cate2),
				(business_disp_cate2,business_line,new_disp_cate2,sloc1),
				(business_disp_cate2,business_line,new_disp_cate2,slot),
				(business_disp_cate2,new_disp_cate2,new_info_cate2,sloc1),
				(business_disp_cate2,new_disp_cate2,new_info_cate2,slot),
				(business_disp_cate2,new_info_cate2,sloc1,slot),
				(business_disp_cate2,business_line,new_disp_cate2,new_info_cate2,sloc1),
				(business_disp_cate2,business_line,new_disp_cate2,new_info_cate2,slot),
				(business_disp_cate2,new_disp_cate2,new_info_cate2,sloc1,slot),
				(business_disp_cate2,business_line,new_disp_cate2,new_info_cate2,sloc1,slot),
				(business_line,new_disp_cate2),
				(business_line,new_info_cate2),
				(business_line,new_disp_cate2,new_info_cate2),
				(business_line,new_disp_cate2,sloc1),
				(business_line,new_disp_cate2,slot),
				(business_line,new_info_cate2,sloc1),
				(business_line,new_info_cate2,slot),
				(business_line,new_disp_cate2,new_info_cate2,sloc1),
				(business_line,new_disp_cate2,new_info_cate2,slot),
				(business_line,new_info_cate2,sloc1,slot),
				(business_line,new_disp_cate2,new_info_cate2,sloc1,slot),
				(new_disp_cate2),
				(new_disp_cate2,new_info_cate2),
				(new_disp_cate2,sloc1),
				(new_disp_cate2,slot),
				(new_disp_cate2,new_info_cate2,sloc1),
				(new_disp_cate2,new_info_cate2,slot),
				(new_disp_cate2,sloc1,slot),
				(new_disp_cate2,new_info_cate2,sloc1,slot),
				(new_info_cate2),
				(new_info_cate2,sloc1),
				(new_info_cate2,slot),
				(new_info_cate2,sloc1,slot)
			)
			HAVING(
				FIND_IN_SET('-',CONCAT_ws(',',if(business_disp_cate2 is null ,'',business_disp_cate2)
				,if(business_line is null ,'',business_line)
				,if(new_disp_cate2 is null ,'',new_disp_cate2)
				,if(new_info_cate2 is null ,'',new_info_cate2)
				,if(sloc1 is null ,'',sloc1)
				,if(slot is null ,'',slot)))=0
			)
) tmp